﻿using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using PetaPoco;
using PmSoft;
using PmSoft.ApplicationContext;
using PmSoft.DBContext;
using PmSoft.Repositories;
using PmSoft.Utilities;
using static Microsoft.Extensions.Logging.EventSource.LoggingEventSource;

namespace Modules.Person.Repositories
{
    [UseDI(ServiceLifetime.Scoped)]
    public class PersonInfoRepository : Repository<PersonInfo, DefaultDbContext>
    {
        private readonly ILogger logger;

        public PersonInfoRepository(DefaultDbContext dbContext, IApplicationContext applicationContext, ILogger<PersonInfoRepository> logger)
            : base(dbContext, applicationContext)
        {
            this.logger = logger;
        }

        public IPagedList<PersonInfo> GetPersons(PersonQuery query, int pageIndex, int pageSize)
        {
            Sql sql = Sql.Builder;
            sql.Select("person_id")
                .From("person");
            if (!string.IsNullOrEmpty(query.Keywords))
                sql.Where("name Like @0", "%" + StringUtility.StripSQLInjection(query.Keywords) + "%");
            sql.OrderBy("create_time DESC");
            return GetPagingEntities(pageSize, pageIndex, sql);
        }

        public bool ExistsByPersonCardNumber(PersonInfo entity)
        {
            if (string.IsNullOrEmpty(entity.CardNumber))
                return false;
            return DbContext.Exists<PersonInfo>("card_number=@0", entity.CardNumber);
        }

        public bool ExistsByPersonCardNumber(PersonInfo data, int? personId)
        {
            if (string.IsNullOrEmpty(data.CardNumber))
                return false;
            if (!personId.HasValue)
                return false;
            return DbContext.Exists<PersonInfo>("card_number=@0 AND person_id!=@1", data.CardNumber, personId);
        }

        /// <summary>
        /// 根据身份证号码获取人员ID
        /// </summary>
        /// <param name="cardNo"></param>
        /// <returns></returns>
        public int? GetPersonIdByCardNo(string cardNo)
        {
            Sql sql = Sql.Builder.Select("person_id").From("person").Where("card_number=@0", cardNo);
            return DbContext.FirstOrDefault<int?>(sql);
        }

        /// <summary>
        /// 根据身份证号码获取人员信息
        /// </summary>
        /// <param name="cardNo"></param>
        /// <returns></returns>
        public PersonInfo? GetPersonByCardNo(string cardNo)
        {
            var id = GetPersonIdByCardNo(cardNo);
            if (id.HasValue && id > 0)
                return Get(id);
            return null;
        }

        public PersonInfo? GetPersonBySecurityId(int securityId)
        {
            Sql sql = Sql.Builder.Select("person_id").From("person").Where("security_id=@0", securityId);
            var id = DbContext.FirstOrDefault<int>(sql);
            if (id == 0)
                return null;
            return Get(id);
        }

        public IEnumerable<PersonInfo> GetPersonByNames(string keywords)
        {
            Sql sql = Sql.Builder;
            sql.Select("person_id")
                .From("person");
            sql.Where("name like @0", "%" + keywords + "%");
            var ids = DbContext.FetchFirstColumn<int>(sql);
            return PopulateEntitiesByEntityIds(ids);
        }

        public IEnumerable<PersonInfo> GetPersonsByCardNumbers(IEnumerable<string> cardNumbers)
        {
            if (cardNumbers == null || cardNumbers.Count() == 0)
                return Enumerable.Empty<PersonInfo>();

            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("person")
                .Where("card_number IN(@0)", cardNumbers);
            var ids = DbContext.FetchFirstColumn<string>(sql);
            return PopulateEntitiesByEntityIds(ids);
        }

        /// <summary>
        /// 通过项目Id获取人员信息
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public IEnumerable<PersonInfo> GetPersonInfosByProjectId(int projectId)
        {
            Sql sql = Sql.Builder;
            sql.Select("person_id")
                .From("project_group_member")
                .Where("project_id=@0", projectId);
            var ids = DbContext.FetchFirstColumn<string>(sql);
            return PopulateEntitiesByEntityIds(ids);
        }

        public IPagedList<PersonInfo> GetPersonInfosByProjectIdAndCorpId(string? keywords, int? projectId, int? corpId, int pageIndex, int pageSize)
        {
            Sql sql = Sql.Builder;
            sql.Select("DISTINCT a.person_id")
                .From("person as a");
            sql.InnerJoin("project_group_member as b").On("a.person_id = b.person_id");
            if (projectId.HasValue)
                sql.Where("project_id=@0", projectId);
            if (corpId.HasValue)
                sql.Where("corp_id=@0", corpId);
            if (!string.IsNullOrEmpty(keywords))
                sql.Where("a.name Like @0", "%" + StringUtility.StripSQLInjection(keywords) + "%");
            sql.OrderBy("b.create_time DESC");
            return GetPagingEntities(pageSize, pageIndex, sql);
        }

        /// <summary>
        /// 通过管理员Id模糊查询人员
        /// </summary>
        /// <param name="cardNumber"></param>
        /// <param name="managerId"></param>
        /// <returns></returns>
        public IEnumerable<PersonInfo> SearchPersonByCardNumber(string cardNumber, int managerId)
        {
            Sql sql = Sql.Builder;
            sql.Select("person.person_id")
                .From("person");
            sql.InnerJoin("project_group_member").On("project_group_member.person_id=person.person_id");
            sql.InnerJoin("manager").On("project_group_member.project_id=manager.project_id");
            sql.Where("manager.manager_id=@0", managerId);
            sql.Where("card_number like @0", "%" + StringUtility.StripSQLInjection(cardNumber) + "%");
            var ids = DbContext.FetchFirstColumn<int>(sql);
            return PopulateEntitiesByEntityIds(ids);
        }
    }
}